package com.ats.daoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.ats.dao.FindInfoDao_cl;
import com.ats.model.Classe;
import com.ats.util.JDBCUtil;

import net.sf.json.JSONArray;

public class FindInfoDaoImpl_cl implements FindInfoDao_cl{

	@Override
	public JSONArray FindInfoByIid(int iid) {
		JSONArray jso = new JSONArray();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT ats_classe.cname,ats_student.`name`,ats_student.age,ats_student.address,ats_student.number ");
		sql.append("FROM ats_student ");
		sql.append("INNER JOIN ats_classe ON ats_student.sid=ats_classe.sid ");
		sql.append("INNER JOIN ats_instructor ");
		sql.append("ON ats_classe.iid=ats_instructor.iid ");
		sql.append("WHERE ats_instructor.iid=?");
		Connection  con = JDBCUtil.getConnection();
		PreparedStatement pst;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1,iid);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				Classe  ca = new Classe();
				ca.setCname(rs.getString("cname"));
				ca.setName(rs.getString("name"));
				ca.setSage(rs.getInt("age"));
				ca.setAddress(rs.getString("address"));
				ca.setNumber(rs.getString("number"));
				jso.add(ca);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return jso;
	}

	@Override
	public JSONArray FindInfoBySid(int sid) {
		JSONArray jso = new JSONArray();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT ats_classe.cname,ats_student.`name`,ats_student.age,ats_student.address,ats_student.number ");
		sql.append("FROM ats_student ");
		sql.append("INNER JOIN ats_classe ON ats_student.sid=ats_classe.sid ");
		sql.append("WHERE ats_student.sid=?");
		Connection  con = JDBCUtil.getConnection();
		PreparedStatement pst;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1,sid);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				Classe  ca = new Classe();
				ca.setCname(rs.getString("cname"));
				ca.setName(rs.getString("name"));
				ca.setSage(rs.getInt("age"));
				ca.setAddress(rs.getString("address"));
				ca.setNumber(rs.getString("number"));
				jso.add(ca);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return jso;
	}

	@Override
	public JSONArray FindInfoByTid(int tid) {
		JSONArray jso = new JSONArray();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT ats_classe.cname,ats_student.`name`,ats_student.age,ats_student.address,ats_student.number ");
		sql.append("FROM ats_student ");
		sql.append("INNER JOIN ats_classe ON ats_student.sid=ats_classe.sid ");
		sql.append("INNER JOIN ats_teacher ");
		sql.append("ON ats_classe.tid=ats_teacher.tid ");
		sql.append("WHERE ats_teacher.tid=?");
		Connection  con = JDBCUtil.getConnection();
		PreparedStatement pst;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1,tid);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				Classe  ca = new Classe();
				ca.setCname(rs.getString("cname"));
				ca.setName(rs.getString("name"));
				ca.setSage(rs.getInt("age"));
				ca.setAddress(rs.getString("address"));
				ca.setNumber(rs.getString("number"));
				jso.add(ca);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return jso;
	}

	

}
